
ALTER    proc sp_close_transit_lots_by_shippng_lot_id (
@process_step_id int,
@lot_number varchar(50),
@location_id int,
@qty_in int,
@date_in datetime )
as 
begin
	declare @previous_processStep_id int
	declare @previous_processStep_type varchar(50)
	declare @previous_processStep_type_name varchar(50)
	declare @lot_id int
	declare @templocation_id int
	declare @record_count int
	declare @tempCursor CURSOR
	declare @locationCursor CURSOR
	
	set @tempCursor = CURSOR for
	select ProcessStep_ID, PS_Type, Process_Type_Name from fn_get_process_flow_by_processstep_id (@process_step_id)
			
	open @tempCursor
	fetch next from @tempCursor
	into @previous_processStep_id, @previous_processStep_type, @previous_processStep_type_name

	while @@fetch_status = 0
	begin
		set @locationCursor = CURSOR for
		select Location_ID from Process_Step_Location_Table where ProcessStep_Id = @previous_processStep_id

		open @locationCursor
		fetch next from @locationCursor into @templocation_id
		
		while @@fetch_status = 0
		begin
			-- update Lot_Table set Lot_Current_Status = 'Close' where ProcessStep_ID = @previous_processStep_id and Lot_Number = @lot_number	and Lot_Current_Status = 'TRANSIT'
			-- here Top 1 is Kept only to Escape from Mallicious data
			set @lot_id = 0
			select top 1 @lot_id = lot_id from lot_table where lot_number = @lot_number and Location_ID = @templocation_id and ProcessStep_ID = @previous_processStep_id --and Lot_Current_Status <> 'Close'

			if @lot_id <> 0
			begin
				select @record_count = Count(*) from Shipped_Lot where lot_id = @lot_id and status = 'Transit' --and Ship_Date <= @date_in and Ship_Qty <= @qty_in
				if @record_count >= 1
				begin
					if @record_count = 1
					begin
						update Shipped_Lot set status = 'Close', Ship_Status = 1 where lot_id = @lot_id 
					end
					else
					begin
						update Shipped_Lot set status = 'Close', Ship_Status = 1 where ShippedLot_ID in ( select * from fn_get_shipping_id_by_qty( @qty_in , @date_in, @lot_id ) ) 
					end
				end
			end
			fetch next from @locationCursor into @templocation_id
		end
		close @locationCursor
		deallocate @locationCursor

		fetch next from @tempCursor
		into @previous_processStep_id, @previous_processStep_type, @previous_processStep_type_name
	end
	close @tempCursor
	deallocate @tempCursor
end
